<?php
class ModelSaleCustomer extends Model {
	public function addCustomer($data) {
		$data['salt'] = substr(md5(uniqid(rand(), true)), 0, 9);
		$data['password'] = sha1($data['salt'] . sha1($data['salt'] . sha1($data['password'])));
		$data['date_added'] = $GLOBALS['V5_NOW'];
      	$customer_id = $this->db->insert('customer', $data);

      	if (isset($data['address'])) {
      		foreach ($data['address'] as $address) {
				$address['customer_id'] = $customer_id;
				$address_id = $this->db->insert('address', $address);
				if (isset($address['default'])) {
					$this->db->query("UPDATE  @@customer SET address_id = $address_id WHERE customer_id = $customer_id");
				}
			}
		}
	}

	public function editCustomer($customer_id, $data) {
		if ($data['password']) {
			$data['salt'] = substr(md5(uniqid(rand(), true)), 0, 9);
			$data['password'] = sha1($data['salt'] . sha1($data['salt'] . sha1($data['password'])));
		}
		else unset($data['password']);
		$this->db->update('customer', $data, array('customer_id' => $customer_id));

      	$this->db->query("DELETE FROM  @@address WHERE customer_id = " . (int)$customer_id);

      	if (isset($data['address'])) {
      		foreach ($data['address'] as $address) {
				$address['customer_id'] = $customer_id;
				$address_id = $this->db->insert('address', $address);
				if (isset($address['default'])) {
					$this->db->query("UPDATE  @@customer SET address_id = $address_id WHERE customer_id = $customer_id");
				}
			}
		}
	}

	public function editToken($customer_id, $token) {
		$this->db->update('customer',
			array('token' => $token),
			array('customer_id' => $customer_id)
		);
	}

	public function deleteCustomer($customer_id) {
		$this->db->query("DELETE FROM  @@customer WHERE customer_id = " . (int)$customer_id);
		$this->db->query("DELETE FROM  @@customer_reward WHERE customer_id = " . (int)$customer_id);
		$this->db->query("DELETE FROM  @@customer_transaction WHERE customer_id = " . (int)$customer_id);
		$this->db->query("DELETE FROM  @@customer_ip WHERE customer_id = " . (int)$customer_id);
		$this->db->query("DELETE FROM  @@address WHERE customer_id = " . (int)$customer_id);
	}

	public function getCustomer($customer_id) {
		return  $this->db->get('customer', array('customer_id' => $customer_id));
	}

	public function getCustomerByEmail($email) {
		return  $this->db->get('customer', array('email' => $email));
	}

	public function getCustomers($filter) {
		$sql = "SELECT c.*, CONCAT(c.firstname, ' ', c.lastname) AS name, cgd.name AS customer_group FROM  @@customer c
				LEFT JOIN  @@customer_group_description cgd ON (c.customer_group_id = cgd.customer_group_id)
				WHERE cgd.language_id = " . (int)C('config_language_id');

		if ($filter->get('filter_name')) {
			$sql .= " AND CONCAT(c.firstname, ' ', c.lastname) LIKE '%" . ES($filter->get('filter_name')) . "%'";
		}

		if ($filter->get('filter_email')) {
			$sql .= " AND c.email LIKE '" . ES($filter->get('filter_email')) . "%'";
		}

		if ($filter->get('filter_newsletter') !== null) {
			$sql .= " AND c.newsletter = " . (int)$filter->get('filter_newsletter');
		}

		if ($filter->get('filter_customer_group_id')) {
			$sql .= " AND c.customer_group_id = " . (int)$filter->get('filter_customer_group_id');
		}

		if ($filter->get('filter_ip')) {
			$sql .= " AND c.customer_id IN (SELECT customer_id FROM  @@customer_ip WHERE ip = '" . ES($filter->get('filter_ip')) . "')";
		}

		if ($filter->get('filter_status') !== null) {
			$sql .= " AND c.status = " . (int)$filter->get('filter_status');
		}

		if ($filter->get('filter_approved') !== null) {
			$sql .= " AND c.approved = " . (int)$filter->get('filter_approved');
		}

		if ($filter->get('filter_date_added')) {
			$sql .= " AND DATE(c.date_added) = DATE('" . ES($filter->get('filter_date_added')) . "')";
		}

		$sql .= $filter->sort('c.customer_id', 'DESC');
		
		$sql .= $filter->limit();
		
		$query = $this->db->query($sql);

		return $query->rows;
	}

	public function approve($customer_id) {
		$customer_info = $this->getCustomer($customer_id);

		if ($customer_info) {
			$this->db->query("UPDATE  @@customer SET approved = 1 WHERE customer_id = " . (int)$customer_id);

			M('setting/store');
			$store_info = $this->model_setting_store->getStore($customer_info['store_id']);

			if ($store_info) {
				$store_name = $store_info['name'];
				$store_url = $store_info['url'];
			} else {
				$store_name = C('config_name');
				$store_url = HTTP_CATALOG;
			}
			
			$params = array(
				'{store_name}' => $store_name,
				'{store_url}' => $store_url,
				'{customer_firstname}' => $customer_info['firstname'],
				'{customer_lastname}' => $customer_info['lastname'],
				'{customer_email}' => $customer_info['email'],
			);
			send_mail($customer_info['email'], 'customer_approve', $params);
		}
	}

	public function getAddress($address_id) {
		$address = $this->db->queryOne("SELECT * FROM  @@address WHERE address_id = " . (int)$address_id);

		if ($address) {
			$country = $this->db->queryOne("SELECT * FROM `@@country` WHERE country_id = " . $address['country_id']);

			if ($country) {
				$address['country'] = $country['name'];
				$address['iso_code_2'] = $country['iso_code_2'];
				$address['iso_code_3'] = $country['iso_code_3'];
				$address['address_format'] = $country['address_format'];
			} else {
				$address['country'] = '';
				$address['iso_code_2'] = '';
				$address['iso_code_3'] = '';
				$address['address_format'] = '';
			}

			$zone = $this->db->queryOne("SELECT * FROM `@@zone` WHERE zone_id = " . $address['zone_id']);

			if ($zone) {
				$address['zone'] = $zone['name'];
				$address['zone_code'] = $zone['code'];
			} else {
				$address['zone'] = '';
				$address['zone_code'] = '';
			}
		}
		return $address;
	}

	public function getAddresses($customer_id) {
		$address_data = array();

		$query = $this->db->query("SELECT address_id FROM  @@address WHERE customer_id = " . (int)$customer_id);

		foreach ($query->rows as $result) {
			$address_info = $this->getAddress($result['address_id']);

			if ($address_info) {
				$address_data[$result['address_id']] = $address_info;
			}
		}

		return $address_data;
	}

	public function getTotalCustomers($filter) {
      	$sql = "SELECT COUNT(*) AS total FROM  @@customer c WHERE 1";

		if ($filter->get('filter_name')) {
			$sql .= " AND CONCAT(c.firstname, ' ', c.lastname) LIKE '%" . ES($filter->get('filter_name')) . "%'";
		}

		if ($filter->get('filter_email')) {
			$sql .= " AND c.email LIKE '" . ES($filter->get('filter_email')) . "%'";
		}

		if ($filter->get('filter_newsletter') !== null) {
			$sql .= " AND c.newsletter = " . (int)$filter->get('filter_newsletter');
		}

		if ($filter->get('filter_customer_group_id')) {
			$sql .= " AND c.customer_group_id = " . (int)$filter->get('filter_customer_group_id');
		}

		if ($filter->get('filter_ip')) {
			$sql .= " AND c.customer_id IN (SELECT customer_id FROM  @@customer_ip WHERE ip = '" . ES($filter->get('filter_ip')) . "')";
		}

		if ($filter->get('filter_status') !== null) {
			$sql .= " AND c.status = " . (int)$filter->get('filter_status');
		}

		if ($filter->get('filter_approved') !== null) {
			$sql .= " AND c.approved = " . (int)$filter->get('filter_approved');
		}

		if ($filter->get('filter_date_added')) {
			$sql .= " AND DATE(c.date_added) = DATE('" . ES($filter->get('filter_date_added')) . "')";
		}

		return $this->db->queryOne($sql);
	}

	public function getTotalCustomersAwaitingApproval() {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@customer WHERE status = 0 OR approved = 0");
	}

	public function getTotalAddressesByCustomerId($customer_id) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@address WHERE customer_id = " . (int)$customer_id);
	}

	public function getTotalAddressesByCountryId($country_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@address WHERE country_id = " . (int)$country_id);
	}

	public function getTotalAddressesByZoneId($zone_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@address WHERE zone_id = " . (int)$zone_id);
	}

	public function getTotalCustomersByCustomerGroupId($customer_group_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@customer WHERE customer_group_id = " . (int)$customer_group_id);
	}

	public function addTransaction($customer_id, $description = '', $amount = '', $order_id = 0) {
		$customer_info = $this->getCustomer($customer_id);

		if ($customer_info) {
			$this->db->query("INSERT INTO  @@customer_transaction SET customer_id = " . (int)$customer_id . ", order_id = " . (int)$order_id . ", description = '" . ES($description) . "', amount = " . (float)$amount . ", date_added = '{$GLOBALS['V5_NOW']}'");

			if ($customer_info['store_id']) {
				M('setting/store');

				$store_info = $this->model_setting_store->getStore($customer_info['store_id']);

				if ($store_info) {
					$store_name = $store_info['name'];
					$store_url = $store_info['url'];
				} else {
					$store_name = C('config_name');
					$store_url = HTTP_CATALOG;
				}
			} else {
				$store_name = C('config_name');
				$store_url = HTTP_CATALOG;
			}
			$params = array(
					'{store_name}' => $store_name,
					'{store_url}' => $store_url,
					'{customer_firstname}' => $customer_info['firstname'],
					'{customer_lastname}' => $customer_info['lastname'],
					'{customer_email}' => $customer_info['email'],
					'{customer_credit_added}' => $this->currency->format($amount, C('config_currency')),
					'{customer_credit_total}' => $this->currency->format($this->getTransactionTotal($customer_id))
			);
			send_mail($customer_info['email'], 'customer_account_credit', $params);
		}
	}

	public function deleteTransaction($order_id) {
		$this->db->query("DELETE FROM  @@customer_transaction WHERE order_id = " . (int)$order_id);
	}

	public function getTransactions($customer_id, $start = 0, $limit = 10) {
		if ($start < 0) $start = 0;
		if ($limit < 1) $limit = 10;

		$query = $this->db->query("SELECT * FROM  @@customer_transaction WHERE customer_id = $customer_id ORDER BY date_added DESC LIMIT $start, $limit");

		return $query->rows;
	}

	public function getTotalTransactions($customer_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total  FROM  @@customer_transaction WHERE customer_id = " . (int)$customer_id);
	}

	public function getTransactionTotal($customer_id) {
		return $this->db->queryOne("SELECT SUM(amount) AS total FROM  @@customer_transaction WHERE customer_id = " . (int)$customer_id);
	}

	public function getTotalTransactionsByOrderId($order_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@customer_transaction WHERE order_id = " . (int)$order_id);
	}

	public function addReward($customer_id, $description = '', $points = '', $order_id = 0) {
		$customer_info = $this->getCustomer($customer_id);

		if ($customer_info) {
			$this->db->query("INSERT INTO  @@customer_reward SET customer_id = " . (int)$customer_id . ", order_id = " . (int)$order_id . ", points = " . (int)$points . ", description = '" . ES($description) . "', date_added = '{$GLOBALS['V5_NOW']}'");

			$this->db->query("UPDATE @@customer SET points = points + $points WHERE customer_id = $customer_id");
			
			$this->updateCustomerGrade($customer_id);
			
			if ($order_id) {
				M('sale/order');

				$order_info = $this->model_sale_order->getOrder($order_id);

				if ($order_info) {
					$store_name = $order_info['store_name'];
					$store_url = $store_info['url'];
				} else {
					$store_name = C('config_name');
					$store_url = HTTP_CATALOG;
				}
			} else {
				$store_name = C('config_name');
				$store_url = HTTP_CATALOG;
			}
			
			$params = array(
					'{store_name}' => $store_name,
					'{store_url}' => $store_url,
					'{customer_firstname}' => $customer_info['firstname'],
					'{customer_lastname}' => $customer_info['lastname'],
					'{customer_email}' => $customer_info['email'],
					'{customer_reward_received}' => $points,
					'{customer_reward_total' => $this->getRewardTotal($customer_id)
			);
			send_mail($customer_info['email'], 'customer_reward_points', $params);
		}
	}

	public function deleteReward($order_id) {
		$reward = $this->db->queryOne("SELECT * FROM @@customer_reward WHERE order_id = $order_id");
		$this->db->query("UPDATE @@customer SET points = points - ". $reward['points'] . " WHERE customer_id = " . $reward['customer)id']);
		$this->updateCustomerGrade($reward['customer_id']);
		
		$this->db->query("DELETE FROM  @@customer_reward WHERE order_id = " . (int)$order_id);
	}

	public function updateCustomerGrade($customer_id) {
		$points = $this->db->queryOne("SELECT points FROM @@customer WHERE customer_id = $customer_id");
		if ($points > 0) {
			$grade = $this->db->queryOne("SELECT grade FROM @@customer_grade WHERE max_points >= $points AND min_points <= $points");
			if ($grade == null) $grade = 0; 
		}
		else {
			$grade = 0;
		}
		$this->db->query("UPDATE @@customer SET grade = $grade WHERE customer_id = $customer_id");
	}
	
	public function getRewards($customer_id, $start = 0, $limit = 10) {
		$query = $this->db->query("SELECT * FROM  @@customer_reward WHERE customer_id = $customer_id ORDER BY date_added DESC LIMIT $start, $limit");

		return $query->rows;
	}

	public function getTotalRewards($customer_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@customer_reward WHERE customer_id = " . (int)$customer_id);
	}

	public function getRewardTotal($customer_id) {
		return $this->db->queryOne("SELECT SUM(points) AS total FROM  @@customer_reward WHERE customer_id = " . (int)$customer_id);
	}

	public function getTotalCustomerRewardsByOrderId($order_id) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@customer_reward WHERE order_id = " . (int)$order_id);
	}

	public function getIpsByCustomerId($customer_id) {
		$query = $this->db->query("SELECT * FROM  @@customer_ip WHERE customer_id = " . (int)$customer_id);

		return $query->rows;
	}

	public function getTotalCustomersByIp($ip) {
		return $this->db->queryOne("SELECT COUNT(*) AS total FROM  @@customer_ip WHERE ip = '" . ES($ip) . "'");
	}

	public function addBlacklist($ip) {
		$this->db->query("INSERT INTO `@@customer_ip_blacklist` SET `ip` = '" . ES($ip) . "'");
	}

	public function deleteBlacklist($ip) {
		$this->db->query("DELETE FROM `@@customer_ip_blacklist` WHERE `ip` = '" . ES($ip) . "'");
	}

	public function getTotalBlacklistsByIp($ip) {
      	return $this->db->queryOne("SELECT COUNT(*) AS total FROM `@@customer_ip_blacklist` WHERE `ip` = '" . ES($ip) . "'");
	}
}
?>